const pool = require('../config/database');

class Alumni {
  static async create({ userId, name, company, position, contactInfo = {}, visibility = 'private', hasOpenings = false }) {
    // Ensure required contact info fields
    const requiredFields = ['email', 'phone', 'industry', 'city'];
    const completeContactInfo = {
      ...contactInfo,
      email: contactInfo.email || '',
      phone: contactInfo.phone || '',
      industry: contactInfo.industry || '',
      city: contactInfo.city || '',
      visibility: contactInfo.visibility || 'private'
    };

    const [result] = await pool.query(
      'INSERT INTO alumni (user_id, name, company, position, contact_info, visibility, hasOpenings) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [userId, name, company, position, JSON.stringify(completeContactInfo), visibility, hasOpenings]
    );
    return result.insertId;
  }

  static async update(id, { name, company, position, contactInfo, visibility, hasOpenings }) {
    const updatedContactInfo = {
      ...contactInfo,
      visibility: contactInfo.visibility || 'private'
    };
    const [result] = pool.query(
      'UPDATE alumni SET name = ?, company = ?, position = ?, contact_info = ?, visibility = ?, hasOpenings = ? WHERE id = ?',
      [name, company, position, JSON.stringify(updatedContactInfo), visibility, hasOpenings, id]
    );
    return result.affectedRows > 0;
  }

  static async getByUserId(userId) {
    const [rows] = await pool.query(
      'SELECT * FROM alumni WHERE user_id = ?',
      [userId]
    );
    return rows[0];
  }

  static async getPublicAlumniByCompany(company) {
    const [rows] = await pool.query(
      'SELECT * FROM alumni WHERE company = ? AND visibility = "public" AND hasOpenings = 1',
      [company]
    );
    return rows;
  }

  static async getAllAlumni({ keyword, industry, page = 1, pageSize = 20 } = {}) {
    const offset = (page - 1) * pageSize;
    let query = 'SELECT * FROM alumni WHERE visibility = "public"';
    const params = [];
    
    // 添加过滤条件
    if (keyword || industry) {
      query += ' AND ';
      const conditions = [];
      
      if (keyword) {
        conditions.push('(name LIKE ? OR company LIKE ?)');
        params.push(`%${keyword}%`, `%${keyword}%`);
      }
      
      if (industry) {
        conditions.push('JSON_UNQUOTE(JSON_EXTRACT(contact_info, "$.industry")) = ?');
        params.push(industry);
      }
      
      query += conditions.join(' AND ');
    }
    
    // 添加分页
    query += ' LIMIT ? OFFSET ?';
    params.push(pageSize, offset);

    console.log('Executing query:', query, 'with params:', params);
    const [rows] = await pool.query(query, params);
    // 确保返回的数据包含hasOpenings字段
    return rows.map(row => ({
      ...row,
      hasOpenings: row.hasOpenings || false
    }));
  }

  static async countAllAlumni({ keyword, industry } = {}) {
    let query = 'SELECT COUNT(*) as total FROM alumni WHERE visibility = "public"';
    const params = [];
    
    if (keyword || industry) {
      query += ' AND ';
      const conditions = [];
      
      if (keyword) {
        conditions.push('(name LIKE ? OR company LIKE ?)');
        params.push(`%${keyword}%`, `%${keyword}%`);
      }
      
      if (industry) {
        conditions.push('JSON_UNQUOTE(JSON_EXTRACT(contact_info, "$.industry")) = ?');
        params.push(industry);
      }
      
      query += conditions.join(' AND ');
    }

    console.log('Executing count query:', query, 'with params:', params);
    const [[result]] = await pool.query(query, params);
    return result.total;
  }

  static async getById(id) {
    const [rows] = await pool.query(
      'SELECT * FROM alumni WHERE id = ?',
      [id]
    );
    if (rows.length === 0) {
      return null;
    }
    const alumni = rows[0];
    // 解析contact_info JSON字段
    if (typeof alumni.contact_info === 'string') {
      alumni.contact_info = JSON.parse(alumni.contact_info);
    }
    return alumni;
  }
}

module.exports = Alumni;
